package networking;
import java.sql.*;
import java.io.*;
import java.sql.DatabaseMetaData;

/**
 *
 * @author miguelkhoury
 * last updated on 07-05-11 at 07:54 PM
 *
 */


/* DON'T FORGET TO CHANGE THE INPUT FILE PATHS BEFORE TESTING.
 * TO TEST THE FOLLOWING CLASS, CREATE MAIN CLASS AND COMPILE:
 *
    import java.io.IOException;
    public class NewMain {
        public static void main(String[] args) throws IOException {
            DBconnector db = new DBconnector();
            db.initialize();
        }
    }
  
 */


public class DBconnector {


    public Connection conn=null;
    public int userCount = 0;
    public int commentCount = 0;
    public int betCount = 0;

    
    public void initialize() throws IOException{
        boolean connected=false;
        while (!connected || conn==null) {
                String user, password;
                BufferedReader reader;
                reader = new BufferedReader(new InputStreamReader(System.in));

                /* System.out.println("Enter mySQL username:");
                user = reader.readLine();
                System.out.println("Enter mySQL password:");
                password = reader.readLine(); */
                user="root"; password="";

                connected = createConnection(user,password);
                if (conn!=null) createDatabase(user, password);
                reader.close();
        }

        String SQLcreateusers="id INT NOT NULL AUTO_INCREMENT,"
               + "PRIMARY KEY (id),"
               + "password VARCHAR(24),"
               + "first_name VARCHAR(20),"
               + "last_name VARCHAR(20),"
               + "birthdate DATE,"
               + "email VARCHAR(60)";

        String SQLcreategame="id INT NOT NULL AUTO_INCREMENT,"
               + "PRIMARY KEY (id),"
               + "team1 INT,"
               + "team2 INT,"
               + "location VARCHAR(60),"
               + "startdate DATE,"
               + "gametime TIME,"
               + "referee VARCHAR(32),"
               + "score1 INT,"
               + "score2 INT,"
               + "events VARCHAR(300),"
               + "comments VARCHAR(32303)," // 32303 = 2*9+3*90+4*900+5*5683
               + "betpot VARCHAR(32303),"   // = 6682 bets and 6682 comments
               + "lastupdate TIME";

        String SQLcreateteam="id INT NOT NULL AUTO_INCREMENT,"
               + "PRIMARY KEY(id),"
               + "name VARCHAR(25),"
               + "players VARCHAR(300),"
               + "teamgroup CHAR(1),"
               + "P INT,"
               + "W INT,"
               + "D INT,"
               + "L INT,"
               + "GF INT,"
               + "GA INT,"
               + "GD INT,"
               + "pts INT";
            // + "img BLOB";

        String SQLcreatecomment="id INT NOT NULL AUTO_INCREMENT,"
               + "PRIMARY KEY(id),"
               + "gameref INT,"
               + "userref INT,"
               + "dateadded DATE,"
               + "timeadded TIME,"
               + "text VARCHAR(200)";

        String SQLcreatebet="id INT NOT NULL AUTO_INCREMENT,"
               + "PRIMARY KEY(id),"
               + "gameref INT,"
               + "userref INT,"
               + "dateadded DATE,"
               + "timeadded TIME,"
               + "status INT,"           // 0 = NOT PLAYED, 1 = WON, -1 = LOST
               + "amount INT";

        createTable("users",SQLcreateusers);
        createTable("game",SQLcreategame);
        createTable("team",SQLcreateteam);
        createTable("comment",SQLcreatecomment);
        createTable("bet",SQLcreatebet);

        // CHANGE PATHS IF NEEDED
        inputFromFile("team","/Users/miguelkhoury/archilochus/team.txt");
        inputFromFile("game","/Users/miguelkhoury/archilochus/game.txt");

        String datapath = "/Users/miguelkhoury/Desktop/team/";
       //importimages(datapath);


        // FUNCTIONS TESTING:
/*
        updateTeamField(5,12,3,true);  //adds 3 points to team 5
        updateTeamField(5,12,3,true);  //adds 3 more points, now pts = 6
        updateTeamField(5,12,3,true);  //adds 3 more points, now pts = 9
        updateTeamField(5,12,3,false); //updates points in such a way now pts=3
        updateTeamField(5,12,1,true);  //adds 1 point to team one, now pts=4
        updateGameScore(5,4,1,"4:27"); //updates score of game 5, become 4-1
        updateGameEvents(5,"I love football!", "4:28");//update event for game 5
        System.out.println(getTeamInfo(5));

        updateTeamField(1,12,3,true);
        updateTeamField(2,12,9,true);updateTeamField(2,11,4,true);
        updateTeamField(2,9,5,true);updateTeamField(2,10,1,true);
        updateTeamField(3,12,9,true);updateTeamField(3,11,4,true);
        updateTeamField(3,9,6,true);updateTeamField(2,10,2,true);
        updateTeamField(4,12,12,true);
        System.out.println(getGroupInfo("A"));

        System.out.println(getTeamGroupRanks("A",1));
        System.out.println(getTeamGroupRanks("A",2));
        System.out.println(getTeamGroupRanks("A",3));
        System.out.println(getTeamGroupRanks("A",4));
        System.out.println(getTeamHomeField(13));
        
        System.out.println(getGames(2,3));
        System.out.println(getGames(5,"2010-11-03"));

        addUser("pass1","marc","farra","1990-10-01","marcfarra@gmail.com");
        addUser("pass2","miguel","khoury","1990-11-20","miguel@gmail.com");
        addUser("pass3","maya","kreidieh","1990-03-17","mayak@gmail.com");
        addUser("pass4","zaher","dawy","1978-06-12","zaherdawy@gmail.com");
        addUser("pass5","mo","mansour","1979-01-26","mmansour@gmail.com");
        System.out.println(getUser(10002));
        System.out.println(searchUser("miguel@gmail.com","pass2"));
        System.out.println(searchUser("miguel@gmail.com","pass3"));
        System.out.println(searchUser("mayak@gmail.com","pass1"));
        System.out.println(searchUser("alfa","pass1"));
        System.out.println(searchUser("mayak@gmail.com","beta"));


        addComment(1,10001,"2011-05-06","17:38","I like this comment 7.");
        addComment(1,10002,"2011-05-06","19:38","I like this comment 6.");
        addComment(1,10003,"2011-05-06","21:38","I like this comment 5.");
        addComment(1,10004,"2011-05-08","15:38","I like this comment 4.");
        addComment(1,10005,"2011-05-08","16:38","I like this comment 3.");
        addComment(1,10003,"2011-05-09","13:38","I like this comment 2.");
        addComment(1,10002,"2011-05-09","21:38","I like this comment 1.");
        System.out.println(getComments(2,1));
        System.out.println(getLastNComments(5));

        System.out.println(getTeamName(3));
        System.out.println(getAllGames(1));

        addBet(1,10001,"2011-05-06","21:39",10);
        addBet(1,10002,"2011-05-06","21:39",20);
        addBet(1,10003,"2011-05-06","21:39",30);
        addBet(1,10004,"2011-05-06","21:39",40);
        addBet(2,10001,"2011-05-06","21:39",50);
        System.out.println(addBet(2,10004,"2011-05-06","21:39",60));
        System.out.println(getBet(2,1));
        System.out.println(getBet(3,10004));
        System.out.println(getLastNBets(3));

        System.out.println(deleteComment(7));
        System.out.println(deleteBet(3));
        System.out.println(getGames(10,"2011-03-01"));
        System.out.println(getGames(10,"2010-09-25"));
        System.out.println(getGames(1,0));
        System.out.println(getNextMatchDayGames("2010-09-23"));
        System.out.println(getGames (8, "FC Barcelona"));
        System.out.println(getGames (9, "FC Twente"));

 */

    }


    private boolean createConnection(String username, String password) {

        String url = "jdbc:mysql://localhost:3306";
        try{
          Class.forName("com.mysql.jdbc.Driver").newInstance();
          conn = DriverManager.getConnection(url,username,password);
          System.out.println("Conection established.");
          return true;
        }
        catch (Exception e){
            System.out.println("Could not connect to mySQL driver.");
//            e.printStackTrace();
            return false;
        }

    }


    private void createDatabase(String username, String password) {

        String url = "jdbc:mysql://localhost:3306/UEFADB";
        try{
            Statement stmt = conn.createStatement();
            stmt.executeUpdate("CREATE DATABASE IF NOT EXISTS UEFADB");
            stmt.executeUpdate("USE UEFADB");
            conn = DriverManager.getConnection(url,username,password);
            System.out.println("Connected to database UEFADB successfully.");
            stmt.close();
        }
        catch (SQLException s){
            System.out.println("Database could not be created. "
                    + "Database might exist already.");
//            s.printStackTrace();
        }
    
  }


    private void createTable(String tablename,String tablecol) {

          try{
              if (checkTableExists(tablename)){
                  System.out.println("Table '"+tablename+"' already exists.");
                  Statement stmt = conn.createStatement(ResultSet.
                          TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
                  ResultSet rs = stmt.executeQuery("SELECT * FROM users");
                  int i=0;
                  while (rs.next()) i++;
                  userCount = i;
                  i=0;
                  rs = stmt.executeQuery("SELECT * FROM comment");
                  while (rs.next()) i++;
                  commentCount = i;
                  i=0;
                  rs = stmt.executeQuery("SELECT * FROM bet");
                  while (rs.next()) i++;
                  betCount = i;
                  rs.close();
                  stmt.close();
              }
              else{
                  Statement stmt = conn.createStatement();
                  stmt.executeUpdate("CREATE TABLE IF NOT EXISTS "
                          +tablename+"("+tablecol+")");
                  System.out.println("Table '"+tablename
                          +"' created successfully.");
                  stmt.close();
              }
          }
          catch(SQLException s){
              System.out.println("Table '"+tablename+"' could not be created.");
//              s.printStackTrace();
          }

    }


    public void closeConnection(){
        try{
            conn.close();
        }
        catch(Exception e){
            System.out.println("Error: could not close connection.");
//            e.printStackTrace();
        }

    }


    private boolean checkTableExists(String tablename){

        DatabaseMetaData dbm = null;
        ResultSet rs = null;
        try{
            dbm = conn.getMetaData();
            rs = dbm.getTables(null, null, tablename, null);
            if (rs.next()) {return true;}
            else {return false;}
        }
        catch(Exception e){
//            e.printStackTrace();
            return false;
        }

    }


    public void inputFromFile (String tablename, String datapath){

        try{
            Statement stmt = conn.createStatement();
            stmt.executeUpdate("LOAD DATA LOCAL INFILE '" + datapath
                + "' INTO TABLE team"
                + " LINES TERMINATED BY '\n'");
            System.out.println("File input from "+datapath+" successful.");
            stmt.close();
        }
        catch(Exception e){
            System.out.println("File input error.");
//            e.printStackTrace();
        }
    }


    public void importimages(String datapath){

        ResultSet rs = null;
        Statement stmt = null;
        File image = null;
        FileInputStream fis = null;
        try{
            stmt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_UPDATABLE);
            rs = stmt.executeQuery("SELECT * FROM team");
            int i=1;
            while(rs.next()){
                image = new File(datapath+i+".png");
                fis = new FileInputStream(image);
                rs.updateBinaryStream(13, (InputStream)fis, (int)(image.length()));
                rs.updateRow();
                i++;
            }
            stmt.close();
            rs.close();
            System.out.println("Team images updated successfully.");
        }
        catch(Exception e){
            System.out.println("Error importing images into team table.");
//            e.printStackTrace();
        }

    }


    // NOTE: FUNCTION TO BE USED ONLY FOR GROUP STAGE (GAMES WITH ID<=96)
    // if updating team points after a game, provide FALSE and game id
    // if updating all games, provide TRUE and last played game gameNO
    public void updateTeamPoints (boolean allTeams,int gameNo) {

        if (gameNo>96) System.out.println("Error: Cannot update game points "
                + "during knockout phase.");
            else{
            ResultSet rs = null;
            Statement stmt = null;
            try{
                stmt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE,
                         ResultSet.CONCUR_READ_ONLY);
                if (allTeams){rs = stmt.executeQuery("SELECT * "
                        + "FROM game where id<='"+gameNo+"'");}
                else rs = stmt.executeQuery("SELECT team1,team2,score1,score2 "
                        +"FROM game WHERE id='"+gameNo+"'");//find game in table
                int team1=0,team2=0,score1=0,score2=0;
                boolean INCREMENT = !allTeams;
                // get the teams and score of either game gameNo or of all games
                while(rs.next()){
                    team1 = rs.getInt("team1");
                    team2 = rs.getInt("team2");
                    score1 = rs.getInt("score1");
                    score2 = rs.getInt("score2");
                    if (team1!=0&&team2!=0){
                        // update games Played
                        updateTeamField(team1,5,1,INCREMENT);
                        updateTeamField(team2,5,1,INCREMENT);
                        // update Goals For
                        updateTeamField(team1,9,score1,INCREMENT);
                        updateTeamField(team2,9,score2,INCREMENT);
                        // update Goals Against
                        updateTeamField(team1,10,score2,INCREMENT);
                        updateTeamField(team2,10,score1,INCREMENT);
                        // update Goals Difference
                        updateTeamField(team1,11,score1-score2,INCREMENT);
                        updateTeamField(team2,11,score2-score1,INCREMENT);
                        if (score1>score2){
                            updateTeamField(team1,6,1,INCREMENT);  // update W
                            updateTeamField(team1,12,3,INCREMENT); // update pts
                            updateTeamField(team2,8,1,INCREMENT);  // update L
                        }
                        else if (score2>score1){
                            updateTeamField(team1,8,1,INCREMENT);  // update L
                            updateTeamField(team2,6,1,INCREMENT);  // update W
                            updateTeamField(team2,12,3,INCREMENT); // update pts
                        }
                        else{
                            updateTeamField(team1,7,1,INCREMENT);  // update D
                            updateTeamField(team2,7,1,INCREMENT);  // update D
                            updateTeamField(team1,12,1,INCREMENT); // update pts
                            updateTeamField(team2,12,1,INCREMENT); // update pts
                        }
                    }
                }
                System.out.println("Team points update succesful,");
                rs.close();
                stmt.close();
            }
            catch(SQLException e){
                System.out.println("Error updating team points.");
//                e.printStackTrace();
            }
        }
    }


    // used to update field values of teams in UpdateTeam() function
    // if increment is TRUE fields values will only be updated by incrementation
    // if increment is FALSE, fields values will be recalculated and stored
     private void updateTeamField (int teamNo,int field,
             int addedvalue,boolean increment){
        
        ResultSet rs = null;
        Statement stmt = null;
        try{
            stmt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_UPDATABLE);
            rs = stmt.executeQuery("SELECT * FROM team");
            rs.absolute(teamNo);    // go to row no. teamNo
            if (increment){ // save old value then insert old value + new value
                int previousvalue = rs.getInt(field);
                rs.updateInt(field,previousvalue+addedvalue);
            }
            else rs.updateInt(field,addedvalue);    // store new value in it
            rs.updateRow();
            rs.close();
            stmt.close();
        }
        catch(SQLException e){
            System.out.println("Error modifying field value.");
//            e.printStackTrace();
        }

     }

     
     // used for live update during the game
     public void updateGameScore (int gameNo, int score1, int score2,
             String time){

        ResultSet rs = null;
        Statement stmt = null;
        try{
            stmt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_UPDATABLE);
            rs = stmt.executeQuery("SELECT * FROM game");
            rs.absolute(gameNo);
            rs.updateInt(8, score1);
            rs.updateInt(9, score2);
            rs.updateString(13, time);
            rs.updateRow();
            System.out.println("Score update successful.");
            stmt.close();
            rs.close();
        }
        catch(SQLException e){
            System.out.println("Error updating scores.");
//            e.printStackTrace();
        }
     }


     // used for updating database after the game
     public void updateGameEvents (int gameNo, String events, String time){

             ResultSet rs = null;
             Statement stmt = null;
             try{
                stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                         ResultSet.CONCUR_UPDATABLE);
                rs = stmt.executeQuery("SELECT * FROM game");
                rs.absolute(gameNo);
                rs.updateString(10, events);
                rs.updateString(13, time);
                rs.updateRow();
                System.out.println("Game events update successful.");
                stmt.close();
                rs.close();
            }
            catch(SQLException e){
                System.out.println("Error updating game events.");
//                e.printStackTrace();
        }

     }


     private void updateGameComments(int gameNo, int commentNo){

         ResultSet rs = null;
         Statement stmt = null;
         try{
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_UPDATABLE);
            rs = stmt.executeQuery("SELECT * FROM game");
            rs.absolute(gameNo);
            String comments = rs.getString("comments");
            if (comments==null) {comments=commentNo+",";}
            else comments = comments + commentNo + ",";
            rs.updateString(11,comments);
            rs.updateRow();
            System.out.println("Game comments update successful.");
            stmt.close();
            rs.close();
        } catch (SQLException e) {
            System.out.println("Error updating game comments.");
//            e.printStackTrace();
        }

    }


     private void updateGameBets(int gameNo, int betNo){

         ResultSet rs = null;
         Statement stmt = null;
         try{
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_UPDATABLE);
            rs = stmt.executeQuery("SELECT * FROM game");
            rs.absolute(gameNo);
            String bets = rs.getString("betpot");
            if (bets==null) {bets=betNo+",";}
            else bets = bets + betNo + ",";
            rs.updateString(12,bets);
            rs.updateRow();
            System.out.println("Game bets update successful.");
            stmt.close();
            rs.close();
        } catch (SQLException e) {
            System.out.println("Error updating game bets.");
 //           e.printStackTrace();
        }

     }


     private boolean insertRow(String tablename, String data){

        String sqlStmt = "INSERT INTO "+tablename+" VALUES("+data+")";
        try {
            Statement stmt = conn.createStatement();
            stmt.executeUpdate(sqlStmt);
            System.out.println("Insertion into table '"
                    +tablename+"' successful.");
            stmt.close();
            return true;
        }
        catch(SQLException e){
            System.out.println("Insertion error.");
//            e.printStackTrace();
            return false;
        }
     }


     public int addUser (String password, String firstname,
             String lastname, String birthdate, String email){

         if (insertRow("users", (userCount+10001)+",'"+password+"','"
                 +firstname+"','"+lastname+"','"+birthdate+"','"+email+"'")){
             userCount++;
             return userCount+10000;
         }
         else return 0;

     }
     
     
     public int addComment (int gameNo, int userID, String dateadded,
             String timeadded, String content){

         if (insertRow("comment", "NULL,"+gameNo+","+userID+",'"
                 +dateadded+"','"+timeadded+"','"+content+"'")){
             commentCount++;
             updateGameComments(gameNo,commentCount);
             
             return commentCount;
         }
         else return 0;

     }


     public int addBet (int gameNo, int userID, String dateadded,
             String timeadded, int amount){

         if (insertRow("bet", "NULL,"+gameNo+","+userID+",'"
                 +dateadded+"','"+timeadded+"',"+0+","+amount)){
             updateGameBets(gameNo,betCount);
             betCount++;
             return betCount;
         }
         else return 0;

     }


     public boolean deleteComment(int commentNo){

        Statement stmt = null;
        try{
            stmt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE,
                 ResultSet.CONCUR_UPDATABLE);
            stmt.executeUpdate("DELETE FROM comment "
                    + "WHERE id = '"+commentNo+"'");
            return true;
         }
        catch(SQLException e){
//            e.printStackTrace();
            return false;
        }

     }


     public boolean deleteBet(int betNo){

        Statement stmt = null;
        try{
            stmt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE,
                 ResultSet.CONCUR_UPDATABLE);
            stmt.executeUpdate("DELETE FROM bet "
                    + "WHERE id = '"+betNo+"'");
            return true;
         }
        catch(SQLException e){
//            e.printStackTrace();
            return false;
        }

     }


     public String getTeamInfo(int teamNo){

        ResultSet rs = null;
        Statement stmt = null;
        try{
            stmt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_READ_ONLY);
            rs = stmt.executeQuery("SELECT * FROM team "
                    + "WHERE id = '"+teamNo+"'");
            rs.next();
            String teaminfo = "<team><1>"+rs.getInt("id")
                    +"><"+rs.getString("name")
                    +"><"+rs.getString("players")
                    +"><"+rs.getString("teamgroup")
                    +"><"+rs.getString("P")
                    +"><"+rs.getString("W")
                    +"><"+rs.getString("D")
                    +"><"+rs.getString("L")
                    +"><"+rs.getString("GF")
                    +"><"+rs.getString("GA")
                    +"><"+rs.getString("GD")
                    +"><"+rs.getString("pts")
                    +"><"+rs.getString("img") +">";
            stmt.close();
            rs.close();
            return teaminfo;
         }
        catch(SQLException e){
            System.out.println("Error: failed to get team's info.");
//            e.printStackTrace();
            return "<team><0>";
        }

     }


     public String getGroupInfo(String groupletter){

        ResultSet rs = null;
        Statement stmt = null;
        try{
            stmt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_READ_ONLY);
            rs = stmt.executeQuery("SELECT * FROM team "
                    + "WHERE teamgroup = '"+groupletter+"' ORDER BY pts DESC, "
                    + "GD DESC, GF DESC");
            String tempteam = null;
            String teaminfo = null;
            int i=0;
            while(rs.next()){
                 tempteam = "<"+rs.getInt("id")
                    +"><"+rs.getString("name")
                    +"><"+rs.getString("players")
                    +"><"+rs.getString("teamgroup")
                    +"><"+rs.getString("P")
                    +"><"+rs.getString("W")
                    +"><"+rs.getString("D")
                    +"><"+rs.getString("L")
                    +"><"+rs.getString("GF")
                    +"><"+rs.getString("GA")
                    +"><"+rs.getString("GD")
                    +"><"+rs.getString("pts")
                    +"><"+rs.getString("img") +">";
                 if (teaminfo==null) teaminfo = tempteam;
                 else teaminfo = teaminfo + tempteam;
                 i++;
            }
            teaminfo = "<team><"+i+">"+teaminfo;
            stmt.close();
            rs.close();
            return teaminfo;
         }
        catch(SQLException e){
            System.out.println("Error: failed to get group's info.");
//            e.printStackTrace();
            return "<team><0>";
        }

     }


     public String getTeamName(int teamNo){
         
        ResultSet rs = null;
        Statement stmt = null;
        try{
            stmt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_READ_ONLY);
            rs = stmt.executeQuery("SELECT * FROM team "
                    + "WHERE id = '"+teamNo+"'");
            rs.next();
            String teamname = rs.getString("name");
            stmt.close();
            rs.close();
            return teamname;

         }
        catch(SQLException e){
            System.out.println("Error: failed to get team's name.");
//            e.printStackTrace();
            return null;
        }
     }


     public int getTeamID(String teamname){

                 ResultSet rs = null;
        Statement stmt = null;
        try{
            stmt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_READ_ONLY);
            rs = stmt.executeQuery("SELECT * FROM team "
                    + "WHERE name = '"+teamname+"'");
            rs.next();
            int teamID = rs.getInt("id");
            stmt.close();
            rs.close();
            return teamID;

         }
        catch(SQLException e){
            System.out.println("Error: failed to get team's ID.");
//            e.printStackTrace();
            return 0;
        }

     }


     public String getUserName(int userID){

        ResultSet rs = null;
        Statement stmt = null;
        try{
            stmt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_READ_ONLY);
            rs = stmt.executeQuery("SELECT * FROM users "
                    + "WHERE id = '"+userID+"'");
            rs.next();
            String username = rs.getString("first_name")
                    +" "+rs.getString("last_name");
            rs.close();
            return username;
         }
        catch(SQLException e){
            System.out.println("Error: failed to get user's name.");
//            e.printStackTrace();
            return null;
        }
         
     }


     // gets all n games that match search criteria of key in a certain field
     // fields:     1 = id,      2 = home team,      3 = away team
     // Output format: <n><game1><game2>...<game n>
     // where format of <game i> is:  <<id><team!><team2><location><startdate>
     // <gametime><referee><score1><score2><events>>
     public String getGames (int field, int key){

        ResultSet rs = null;
        Statement stmt = null;
        String gameInfo = null;
        String tempInfo = null;
        try{
            stmt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_READ_ONLY);
            if (field==1)
                rs = stmt.executeQuery("SELECT * FROM game "
                    + "WHERE id='"+key+"' ORDER BY startdate");
            else if (field==2)
                rs = stmt.executeQuery("SELECT * FROM game "
                    + "WHERE team1 = '"+key+"' ORDER BY startdate");
            else if (field==3)
                rs = stmt.executeQuery("SELECT * FROM game "
                    + "WHERE team2='"+key+"' ORDER BY startdate");
            else return null;
            int i = 0;
            if (!rs.next()) gameInfo = "<game><0>";
            else{
                rs.previous();
                while(rs.next()){
                    if (rs.getString("team1")!=null){
                        tempInfo = "<" +rs.getInt("id")
                                +"><"+getTeamName(rs.getInt("team1"))
                                +"><"+getTeamName(rs.getInt("team2"))
                                +"><"+rs.getString("location")
                                +"><"+rs.getString("startdate")
                                +"><"+rs.getString("gametime")
                                +"><"+rs.getString("referee")
                                +"><"+rs.getInt("score1")
                                +"><"+rs.getInt("score2")
                                +"><"+rs.getString("events")
                                +"><"+rs.getString("lastupdate") + ">";
                        if (i==0) gameInfo = tempInfo;
                        else gameInfo = gameInfo+tempInfo;
                        i++;
                    }
                }
                gameInfo = "<game><"+i+">"+gameInfo;
            }
            stmt.close();
            rs.close();
            return gameInfo;
         }
        catch(SQLException e){
            System.out.println("Could not retrieve games info.");
//            e.printStackTrace();
            return "<game><0>";
        }

     }

     
     // same as above function but this time key is a String
     // fields:     4 = location,      5 = startdate,    7 = referee
     //             8 = home team,     9 = away team
     public String getGames (int field, String key){

        ResultSet rs = null;
        Statement stmt = null;
        String gameInfo = null;
        String tempInfo = null;
        try{
            stmt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_READ_ONLY);
            if (field==4)
                rs = stmt.executeQuery("SELECT * FROM game "
                    + "WHERE location ='"+key+"' ORDER BY id");
            else if (field==5)
                rs = stmt.executeQuery("SELECT * FROM game "
                    + "WHERE startdate = '"+key+"' ORDER BY id");
            else if (field==7)
                rs = stmt.executeQuery("SELECT * FROM game "
                    + "WHERE referee ='"+key+"' ORDER BY id");
            else if (field==8)
                rs = stmt.executeQuery("SELECT * FROM game "
                    + "WHERE team1 = '"+getTeamID(key)+"' ORDER BY id");
            else if (field==9)
                rs = stmt.executeQuery("SELECT * FROM game "
                    + "WHERE team2 ='"+getTeamID(key)+"' ORDER BY id");
            else if (field==10)
                rs = stmt.executeQuery("SELECT * FROM game "
                    + "WHERE startdate <='"+key+"' ORDER BY id");
            else return null;
            int i = 0;
            if (!rs.next()) gameInfo = "<game><0>";
            else{
                rs.previous();
                while(rs.next()){
                    if (rs.getString("team1")!=null){
                        tempInfo = "<" +rs.getInt("id")
                                +"><"+getTeamName(rs.getInt("team1"))
                                +"><"+getTeamName(rs.getInt("team2"))
                                +"><"+rs.getString("location")
                                +"><"+rs.getString("startdate")
                                +"><"+rs.getString("gametime")
                                +"><"+rs.getString("referee")
                                +"><"+rs.getInt("score1")
                                +"><"+rs.getInt("score2")
                                +"><"+rs.getString("events")
                                +"><"+rs.getString("lastupdate") + ">";
                        if (i==0) gameInfo = tempInfo;
                        else gameInfo = gameInfo+tempInfo;
                        i++;
                    }
                }
                gameInfo = "<game><"+i+">"+gameInfo;
            }
            stmt.close();
            rs.close();
            return gameInfo;
         }
        catch(SQLException e){
            System.out.println("Could not retrieve games info.");
//            e.printStackTrace();
            return "<game><0>";
        }

     }


     public String getNextMatchDayGames(String date){

        ResultSet rs = null;
        Statement stmt = null;
        String gameInfo = null;
        String tempInfo = null;
        String newdate = null;
        try{
            stmt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_READ_ONLY);
            rs = stmt.executeQuery("SELECT * FROM game "
                    + "WHERE startdate >='"+date+"' ORDER BY id");
            if (rs.next()) newdate = rs.getString("startdate");
            else gameInfo = "<game><0>";

            rs = stmt.executeQuery("SELECT * FROM game "
                    + "WHERE startdate ='"+newdate+"' ORDER BY id");
            int i = 0;
            if (!rs.next()) gameInfo = "<game><0>";
            else{
                rs.previous();
                while(rs.next()){
                    if (rs.getString("team1")!=null){
                        tempInfo = "<" +rs.getInt("id")
                                +"><"+getTeamName(rs.getInt("team1"))
                                +"><"+getTeamName(rs.getInt("team2"))
                                +"><"+rs.getString("location")
                                +"><"+rs.getString("startdate")
                                +"><"+rs.getString("gametime")
                                +"><"+rs.getString("referee")
                                +"><"+rs.getInt("score1")
                                +"><"+rs.getInt("score2")
                                +"><"+rs.getString("events")
                                +"><"+rs.getString("lastupdate") + ">";
                        if (i==0) gameInfo = tempInfo;
                        else gameInfo = gameInfo+tempInfo;
                        i++;
                    }
                }
                gameInfo = "<game><"+i+">"+gameInfo;
            }
            stmt.close();
            rs.close();
            return gameInfo;
         }
         catch(SQLException e){
            System.out.println("Could not retrieve next match day games.");
//            e.printStackTrace();
            return "<game><0>";
        }

     }


     // get all games either by date (field = 1), by location (field = 4)
     // or by referee (field = 7)
     public String getAllGames(int field){

        ResultSet rs = null;
        Statement stmt = null;
        String gameInfo = null;
        String tempInfo = null;
        try{
            stmt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_READ_ONLY);
            if (field==1)
                rs = stmt.executeQuery("SELECT * FROM game ORDER BY id");
            else if (field==4)
                rs = stmt.executeQuery("SELECT * FROM game ORDER BY location");
            else if (field==7)
                rs = stmt.executeQuery("SELECT * FROM game ORDER BY referee");
            else return null;
            int i = 0;
            while(rs.next()){
                if (rs.getString("team1")!=null){
                    tempInfo = "<" +rs.getInt("id")
                            +"><"+getTeamName(rs.getInt("team1"))
                            +"><"+getTeamName(rs.getInt("team2"))
                            +"><"+rs.getString("location")
                            +"><"+rs.getString("startdate")
                            +"><"+rs.getString("gametime")
                            +"><"+rs.getString("referee")
                            +"><"+rs.getInt("score1")
                            +"><"+rs.getInt("score2")
                            +"><"+rs.getString("events")
                            +"><"+rs.getString("lastupdate") + ">";
                    if (i==0) gameInfo = tempInfo;
                    else gameInfo = gameInfo+tempInfo;
                    i++;
                }
            }
            gameInfo = "<game><"+i+">"+gameInfo;
            stmt.close();
            rs.close();
            return gameInfo;
         }
        catch(SQLException e){
            System.out.println("Could not retrieve all games.");
//            e.printStackTrace();
            return "<game><0>";
        }

     }


     // get comments by comment id (field=1), user (field=2) or game (field=3)
     public String getComments (int field, int key){

        ResultSet rs = null;
        Statement stmt = null;
        String comment = null;
        String tempComment = null;
        try{
            stmt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_READ_ONLY);
            if (field==1)
                rs = stmt.executeQuery("SELECT * FROM comment "
                    + "WHERE id = '"+key+"' ORDER BY id");
            else if (field==2)
                rs = stmt.executeQuery("SELECT * FROM comment "
                    + "WHERE gameref = '"+key+"' ORDER BY id");
            else if (field==3)
                rs = stmt.executeQuery("SELECT * FROM comment "
                    + "WHERE userref = '"+key+"' ORDER BY id");
            else return null;
            int i = 0;
            while(rs.next()){
                tempComment = "<" +rs.getInt("id")
                        +"><"+getUserName(rs.getInt("userref"))
                        +"><"+rs.getString("dateadded")
                        +"><"+rs.getString("timeadded")
                        +"><"+rs.getString("text") +">";
                if (i==0) comment = tempComment;
                else comment = comment+tempComment;
                i++;
            }
            comment = "<comment><"+i+">"+comment;
            stmt.close();
            rs.close();
            return comment;
         }
        catch(SQLException e){
            System.out.println("Could not retrieve comment.");
//            e.printStackTrace();
            return "<comment><0>";
        }

     }


     // gets the last N comments, to be used for client's home page right column
     public String getLastNComments(int N){

        if (N>commentCount) return "<comment><0>";
        ResultSet rs = null;
        Statement stmt = null;
        String comment = null;
        String tempComment = null;
        try{
            stmt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_READ_ONLY);
            rs = stmt.executeQuery("SELECT * FROM comment "
                    + "WHERE id > '"+(commentCount-N)+"' ORDER BY id");
            int i=0;
            while(rs.next()){
                tempComment = "<" +rs.getInt("id")
                        +"><"+getUserName(rs.getInt("userref"))
                        +"><"+rs.getString("dateadded")
                        +"><"+rs.getString("timeadded")
                        +"><"+rs.getString("text") +">";
                if (i==0) comment = tempComment;
                else comment = comment+tempComment;
                i++;
            }
            comment = "<comment><"+i+">"+comment;
            stmt.close();
            rs.close();
            return comment;
         }
        catch(SQLException e){
            System.out.println("Could not retrieve last "+N+" comments.");
//            e.printStackTrace();
            return "<bet><0>";
        }

     }

     
     // get user info by ID
     // format returned: <id><first name><last name><birthday>
     public String getUser(int userID){

        ResultSet rs = null;
        Statement stmt = null;
        try{
            stmt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_READ_ONLY);
            rs = stmt.executeQuery("SELECT * FROM users "
                    + "WHERE id = '"+userID+"'");
            rs.next();
            String user =  "<user>"+"<"+ rs.getInt("id")
                    +"><"+rs.getString("first_name")
                    +"><"+rs.getString("last_name")
                    +"><"+rs.getString("birthdate")
                    +"><"+rs.getString("email") +">";
            stmt.close();
            rs.close();
            return user;
        }
        catch(SQLException e){
            System.out.println("Could not retrieve user info.");
//            e.printStackTrace();
            return "<user><0>";
        }

     }


     public int searchUser (String email, String password){

        ResultSet rs = null;
        Statement stmt = null;
        try{
            stmt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_READ_ONLY);
            rs = stmt.executeQuery("SELECT * FROM users "
                    + "WHERE email = '"+email+"' AND password = "
                    + "'"+password+"'");
            rs.next();
            int userID = rs.getInt("id");
            stmt.close();
            rs.close();
            return userID;

        }
        catch(SQLException e){
            System.out.println("Could not retrieve user info.");
//            e.printStackTrace();
            return 0;
        }

     }

     // get bet by id (field = 1), game (field = 2), user (field = 3)
     // or date added (field = 4)
     public String getBet(int field, int key){

        ResultSet rs = null;
        Statement stmt = null;
        String bet = null;
        String tempBet = null;
        try{
            stmt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_READ_ONLY);
            if (field==1)
                rs = stmt.executeQuery("SELECT * FROM bet "
                    + "WHERE id = '"+key+"' ORDER BY id");
            else if (field==2)
                rs = stmt.executeQuery("SELECT * FROM bet "
                    + "WHERE gameref = '"+key+"' ORDER BY id");
            else if (field==3)
                rs = stmt.executeQuery("SELECT * FROM bet "
                    + "WHERE userref = '"+key+"' ORDER BY id");
            else if (field==10)
                rs = stmt.executeQuery("SELECT * FROM bet "
                    + "WHERE status = '"+key+"' ORDER BY id");
            else return null;
            int i = 0;
            if (!rs.next()) bet = "<bet><0>";
            else{
                    rs.previous();
                    while(rs.next()){
                    tempBet = "<" +rs.getInt("id")
                            +"><"+rs.getString("gameref")
                            +"><"+getUserName(rs.getInt("userref"))
                            +"><"+rs.getString("dateadded")
                            +"><"+rs.getString("timeadded")
                            +"><"+rs.getInt("status")
                            +"><"+rs.getInt("amount") +">";
                    if (i==0) bet = tempBet;
                    else bet = bet+tempBet;
                    i++;
                }
                bet = "<bet><"+i+">"+bet;
                stmt.close();
                rs.close();
            }
            return bet;
         }
        catch(SQLException e){
            System.out.println("Could not retrieve bet.");
//            e.printStackTrace();
            return "<bet><0>";
        }

     }


     public String getLastNBets(int N){

        if (N>betCount) return "<bet><0>";
        ResultSet rs = null;
        Statement stmt = null;
        String bet = null;
        String tempBet = null;
        try{
            stmt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_READ_ONLY);
            rs = stmt.executeQuery("SELECT * FROM bet "
                    + "WHERE id > '"+(betCount-N)+"' ORDER BY id");
            int i=0;
            while(rs.next()){
                tempBet = "<" +rs.getInt("id")
                        +"><"+rs.getString("gameref")
                        +"><"+getUserName(rs.getInt("userref"))
                        +"><"+rs.getString("dateadded")
                        +"><"+rs.getString("timeadded")
                        +"><"+rs.getInt("status")
                        +"><"+rs.getInt("amount") +">";
                if (i==0) bet = tempBet;
                else bet = bet+tempBet;
                i++;
            }
            bet = "<bet><"+i+">"+bet;
            stmt.close();
            rs.close();
            return bet;
         }
        catch(SQLException e){
            System.out.println("Could not retrieve last "+N+" bets.");
//            e.printStackTrace();
            return "<bet><0>";
        }

     }


     // update games info for gameID>96, i.e. during the knockout phase
     // if lastGameOnly is true, update only last game played
     // if lastGameOnly is false, update all games before last game played
     public void updateTeamRankings(boolean lastGameOnly,int lastPlayedGameNo){

        int i;
        if (lastGameOnly) i = lastPlayedGameNo;
        else i = 97;
        
        while(i<=lastPlayedGameNo){

        // Round of 16, 1st leg
        if(i==97){
            int winnerA = getTeamGroupRanks("A",1);
            int runnerupG = getTeamGroupRanks("G",2);
            updateGameTeams(i,runnerupG,winnerA,getTeamHomeField(runnerupG));
        }
        else if(i == 98){
            int winnerB = getTeamGroupRanks("B",1);
            int runnerupC = getTeamGroupRanks("C",2);
            updateGameTeams(i,runnerupC,winnerB,getTeamHomeField(runnerupC));
        }
        else if(i == 99){
            int winnerH = getTeamGroupRanks("H",1);
            int runnerupE = getTeamGroupRanks("E",2);
            updateGameTeams(i,runnerupE,winnerH,getTeamHomeField(runnerupE));
        }
        else if(i == 100){
            int winnerD = getTeamGroupRanks("D",1);
            int runnerupH = getTeamGroupRanks("H",2);
            updateGameTeams(i,runnerupH,winnerD,getTeamHomeField(runnerupH));
        }
        else if(i == 101){
            int winnerF = getTeamGroupRanks("F",1);
            int runnerupD = getTeamGroupRanks("D",2);
            updateGameTeams(i,runnerupD,winnerF,getTeamHomeField(runnerupD));
        }
        else if(i == 102){
            int winnerG = getTeamGroupRanks("G",1);
            int runnerupB = getTeamGroupRanks("B",2);
            updateGameTeams(i,runnerupB,winnerG,getTeamHomeField(runnerupB));
        }
        else if(i == 103){
            int winnerC = getTeamGroupRanks("C",1);
            int runnerupF = getTeamGroupRanks("F",2);
            updateGameTeams(i,runnerupF,winnerC,getTeamHomeField(runnerupF));
        }
        else if(i == 104){
            int winnerE = getTeamGroupRanks("E",1);
            int runnerupA = getTeamGroupRanks("A",2);
            updateGameTeams(i,runnerupA,winnerE,getTeamHomeField(runnerupA));
        }
            
        // Round of 16, 2nd leg
        else if(i == 105){
            int winnerH = getTeamGroupRanks("H",1);
            int runnerupE = getTeamGroupRanks("E",2);
            updateGameTeams(i,winnerH,runnerupE,getTeamHomeField(winnerH));
        }
        else if(i == 106){
            int winnerD = getTeamGroupRanks("D",1);
            int runnerupH = getTeamGroupRanks("H",2);
            updateGameTeams(i,winnerD,runnerupH,getTeamHomeField(winnerD));
        }
        else if(i == 107){
            int winnerA = getTeamGroupRanks("A",1);
            int runnerupG = getTeamGroupRanks("G",2);
            updateGameTeams(i,winnerA,runnerupG,getTeamHomeField(winnerA));
        }
        else if(i == 108){
            int winnerB = getTeamGroupRanks("B",1);
            int runnerupC = getTeamGroupRanks("C",2);
            updateGameTeams(i,winnerB,runnerupC,getTeamHomeField(winnerB));
        }
        else if(i == 109){
            int winnerC = getTeamGroupRanks("C",1);
            int runnerupF = getTeamGroupRanks("F",2);
            updateGameTeams(i,winnerC,runnerupF,getTeamHomeField(winnerC));
        }
        else if(i == 110){
            int winnerE = getTeamGroupRanks("E",1);
            int runnerupA = getTeamGroupRanks("A",2);
            updateGameTeams(i,winnerE,runnerupA,getTeamHomeField(winnerE));
        }
        else if(i == 111){
            int winnerF = getTeamGroupRanks("F",1);
            int runnerupD = getTeamGroupRanks("D",2);
            updateGameTeams(i,winnerF,runnerupD,getTeamHomeField(winnerF));
        }
        else if(i == 112){
            int winnerG = getTeamGroupRanks("G",1);
            int runnerupB = getTeamGroupRanks("B",2);
            updateGameTeams(i,winnerG,runnerupB,getTeamHomeField(winnerG));
        }

        // Quarter-finals, 1st leg
        else if(i == 113){
            int winner102 = getWinner(102,112);
            int winner97 = getWinner(97,107);
            updateGameTeams(i,winner102,winner97,getTeamHomeField(winner102));
        }
        else if(i == 114){
            int winner104 = getWinner(104,110);
            int winner98 = getWinner(98,108);
            updateGameTeams(i,winner104,winner98,getTeamHomeField(winner104));
        }
        else if(i == 115){
            int winner101 = getWinner(101,111);
            int winner103 = getWinner(103,109);
            updateGameTeams(i,winner101,winner103,getTeamHomeField(winner101));
        }
        else if(i == 116){
            int winner100 = getWinner(100,106);
            int winner99 = getWinner(99,105);
            updateGameTeams(i,winner100,winner99,getTeamHomeField(winner100));
        }

        // Quarter-finals, 2nd leg
        else if(i == 117){
            int winner99 = getWinner(99,105);
            int winner100 = getWinner(100,106);
            updateGameTeams(i,winner99,winner100,getTeamHomeField(winner99));
        }
        else if(i == 118){
            int winner103 = getWinner(103,109);
            int winner101 = getWinner(101,111);
            updateGameTeams(i,winner103,winner101,getTeamHomeField(winner103));
        }
        else if(i == 119){
            int winner98 = getWinner(98,108);
            int winner104 = getWinner(104,110);
            updateGameTeams(i,winner98,winner104,getTeamHomeField(winner98));
        }
        else if(i == 120){
            int winner97 = getWinner(97,107);
            int winner102 = getWinner(102,112);
            updateGameTeams(i,winner97,winner102,getTeamHomeField(winner97));
        }

        // Semi-finals, 1st leg
        else if(i == 121){
            int winner114 = getWinner(114,119);
            int winner115 = getWinner(115,118);
            updateGameTeams(i,winner114,winner115,getTeamHomeField(winner114));
        }
        else if(i == 122){
            int winner113 = getWinner(113,120);
            int winner116 = getWinner(116,117);
            updateGameTeams(i,winner113,winner116,getTeamHomeField(winner113));
        }

        // Semi-finals, 2nd leg
        else if(i == 123){
            int winner116 = getWinner(116,117);
            int winner113 = getWinner(113,120);
            updateGameTeams(i,winner116,winner113,getTeamHomeField(winner116));
        }
        else if(i == 124){
            int winner115 = getWinner(115,118);
            int winner114 = getWinner(114,119);
            updateGameTeams(i,winner115,winner114,getTeamHomeField(winner115));
        }

        // FINAL
        else if(i == 125){
            int winner122 = getWinner(122,123);
            int winner121 = getWinner(121,124);
            updateGameTeams(i,winner122,winner121,
                    "Wembley Stadium, London (ENG)");
        }

        i++;
        }

    }

     
     private int getTeamGroupRanks(String groupname, int rank){

        ResultSet rs = null;
        Statement stmt = null;
        try{
            stmt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_READ_ONLY);
                rs = stmt.executeQuery("SELECT * FROM team "
                    + "WHERE teamgroup = '"+groupname+"' ORDER BY pts DESC, "
                    + "GD DESC, GF DESC");
            while(rank!=0){
                rs.next();
                rank--;
            }
            int teamid = rs.getInt("id");
            stmt.close();
            rs.close();
            return teamid;
        }
        catch(SQLException e){
            System.out.println("Could not retrieve group winners.");
//            e.printStackTrace();
            return 0;
        }

    }


    private void updateGameTeams (int gameNo, int team1, int team2,
            String location){

        ResultSet rs = null;
        Statement stmt = null;
        try{
            stmt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_UPDATABLE);
            rs = stmt.executeQuery("SELECT * FROM game");
            rs.absolute(gameNo);
            rs.updateInt(2, team1);
            rs.updateInt(3, team2);
            rs.updateString(4, location);
            rs.updateRow();
            stmt.close();
            rs.close();
            System.out.println("Game teams updated successfully.");
        }
        catch(SQLException e){
            System.out.println("Error updating game teams.");
//            e.printStackTrace();
        }

     }


     private String getTeamHomeField(int teamNo){

        ResultSet rs = null;
        Statement stmt = null;
        try{
            stmt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_READ_ONLY);
            rs = stmt.executeQuery("SELECT * FROM game "
                    + "WHERE team1 = '"+teamNo+"'");
            rs.next();
            String location = rs.getString("location");
            stmt.close();
            rs.close();
            return location;
        }
        catch(SQLException e){
            System.out.println("Could not retrieve team's home field.");
//            e.printStackTrace();
            return null;
        }

     }


     private int getWinner(int game1, int game2){

        ResultSet rs = null;
        Statement stmt = null;
        String bet = null;
        String tempBet = null;
        try{
            stmt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_READ_ONLY);
                rs = stmt.executeQuery("SELECT * FROM team "
                    + "WHERE id = '"+game1+"' OR id = '"+game2+"'");
                rs.next();
                int team1 = rs.getInt("team1");
                int team2 = rs.getInt("team2");
                int score1home = rs.getInt("score1");
                int score2away = rs.getInt("score2");
                rs.next();
                int score2home = rs.getInt("score1");
                int score1away = rs.getInt("score2");

                stmt.close();
                rs.close();
                if (score1home+score1away>score2home+score2away)
                    return team1;
                else if (score2home+score2away>score1home+score1away)
                    return team2;
                else{
                    if (score1away>score2away) return team1;
                    else if (score2away>score1away) return team2;
                    else{ //impossible situation cause resolved by extra time
                        return 0;
                    }
                }
        }
        catch(SQLException e){
            System.out.println("Could not retrieve game winners.");
//            e.printStackTrace();
            return 0;
        }

     }

     
}